package edu.uwm.universitydb.tests;

public class Query3 extends PerformanceTest {

	@Override
	public String getName() {
		return "Professor who specializes in \"Nano engineering\" and currently not working on any projects";
	}
	
	@Override
	public String getQuery() {
		return "SELECT prof.name " +
				"FROM professor prof " +
				"WHERE prof.prof_ssn NOT IN ( " +
					"SELECT prof2.prof_ssn " +
					"FROM Professor prof2, Project proj, co_investigate coinv  " +
					"WHERE prof2.prof_ssn = coinv.prof_ssn AND proj.pno = coinv.pno) " +
				"AND prof.research_speciality = 'NanoEngineering'";
	}

	@Override
	public String[] getAddIndex() {
		return new String [] {
				"create index speciality_index on professor (research_speciality)  using hash;"
		};
	}

	@Override
	String[] getDropIndex() {
		return new String [] {
				"drop index speciality_index on professor"
		};
	}
}
 
